class SyllabusController < ApplicationController
  layout 'main'
  after_filter :post_log
  before_filter :admin?, :only=>[:admin, :new, :delete, :edit, :create, :update, :assign_course, :mapper, :manage_pre_requisite, :save_pre_requisite, :opening, :new_opening, :create_opening, :edit_opening, :update_opening, :delete_opening]
  
  def admin
    @syllabuses= Syllabus.find :all, :order=>:id
  end
  def new
  end
  def show_syllabus_registration
    @syllabuses = []
    registrations = Registration.find :all, :conditions => ["user_id=?",current_user.id], :order => "id ASC"
    if(registrations.present?)
      registrations.each do |item|
        if item.syllabus_opening
          if(not @syllabuses.include?(item.syllabus_opening.syllabus))
            @syllabuses << item.syllabus_opening.syllabus
          end
        end
      end
    end
  end
  def certificate_request
    #@syllabuses= Syllabus.find :all, :order=>:id
    @syllabuses = []
    registrations = Registration.find :all, :conditions => ["user_id=?",current_user.id], :order => "id ASC"
    if(registrations.present?)
      registrations.each do |item|
        if item.syllabus_opening
          if(not @syllabuses.include?(item.syllabus_opening.syllabus))
            @syllabuses << item.syllabus_opening.syllabus
          end
        end
      end
    end
  end
  def check_certificate
    @syllabus = Syllabus.find params[:id]
    user = current_user
    #@registration_courses = RegistrationCourse.find :all, :joins => 'inner join registrations on registration_courses.registration_id=registrations.id', :conditions => ["registration_courses.absent=? and registrations.user_id=? and registration_courses.percent is not null",false,current_user.id], :order => "id ASC"
    result = Registration.find_by_sql "select r.id from registrations r inner join syllabus_openings so on r.syllabus_opening_id = so.id inner join syllabuses s on so.syllabus_id = s.id where s.id="+@syllabus.id.to_s+" and r.user_id="+user.id.to_s
    if(result.present?)
      @registration = Registration.find result[0].id
      @mapper = SyllabusCourseMapper.find_all_by_syllabus_id @syllabus.id
      @unpass_course = []
      @mapper.each do |item|
#        registration_course = RegistrationCourse.find :first, :conditions=>["registration_id=? and course_id=?",@registration.id,item.course.id]
        registration_course = nil
        tmp_registration_course = RegistrationCourse.find_by_sql("select registration_courses.id as id, coalesce(registration_courses.grade, 'Z') as grade from registration_courses inner join registrations on registration_courses.registration_id = registrations.id and registration_courses.course_id = "+item.course.id.to_s+" and registrations.user_id = "+current_user.id.to_s+" order by grade ASC")
        if tmp_registration_course.present?
          registration_course = RegistrationCourse.find_by_id(tmp_registration_course[0].id)
        end
        if(registration_course.blank?)
          flash[:notice] = 'ท่านลงเรียนไม่ครบทุกกระบวนวิชาของหลักสูตรนี้'
          #          redirect_to :action => 'certificate_request'
          return
        else
          cert_course_rule = CertificateCourseRule.find :first, :conditions=>["syllabus_id=? and course_id=?",@syllabus.id,item.course.id]
          if(cert_course_rule.present?)
            if(registration_course.grade.present? and cert_course_rule.minimum_grade.present? and registration_course.grade > cert_course_rule.minimum_grade)  # A < B < C < D < F หรือ S < U เงื่อนไขคือ ตก
              @unpass_course << registration_course
            elsif(registration_course.grade.blank?)
              @unpass_course << registration_course
            end
          end
        end
      end
      if(@unpass_course.size()>0)
        flash[:notice] = 'ท่านไม่ผ่านบางกระบวนวิชาในหลักสูตรนี้'
        #        redirect_to :action => 'certificate_request',
        return
      else
        cert = Certificate.find_by_registration_id(@registration.id)
        if(cert.blank?)
          year = (Date.today).year+543
          max = 1
          cert_item = Certificate.find_by_sql("select max(ref_code) as ref_code from certificates where ref_code like '%/"+year.to_s+"'")
          if(cert_item.present?)
            old_ref_code = cert_item.collect(&:ref_code)
            if old_ref_code[0].present?
              id, year = old_ref_code[0].split("/")
              max = id.to_i+1
            end
          end
          @ref_code = max.to_s+'/'+year.to_s
          certificate = Certificate.new
          certificate.registration = @registration
          certificate.ref_code = @ref_code
          certificate.save
        else
          @ref_code = cert.ref_code
        end
        flash[:notice] = 'ท่านผ่านหลักสูตร<br/>รหัสใบรับรอง :: '+@ref_code
        #        redirect_to :action => 'certificate_request'
      end
    else
      flash[:notice] = 'ไม่พบการลงทะเบียนหลักสูตรที่ท่านเลือก'
      redirect_to :action => 'certificate_request'
    end
  end
  def check_certificate_ref_code
  end
  def confirm_certificate_ref_code
    @certificate = Certificate.find_by_ref_code(params[:certificate][:ref_code])
  end
  def select_certificate
    @syllabuses= Syllabus.find :all, :order=>:id
  end
  def show_syllabus_openings
    @syllabus
    if(params[:syllabus_id].present?)
      @syllabus = Syllabus.find params[:syllabus_id]
    end
    @syllabus_openings
    if(@syllabus.present?)
      @syllabus_openings = @syllabus.syllabus_opening
    end
    render :layout => false
  end
  def select_certificate2
    @syllabuses= Syllabus.find :all, :order=>:id
  end
  def show_registration_for_print
    sql = """
      select distinct users.title, users.fname, users.lname, syllabuses.name, users.id as user_id, syllabuses.id as syllabus_id
      from registrations
      left join syllabus_openings on syllabus_openings.id = registrations.syllabus_opening_id
      left join syllabuses on syllabuses.id = syllabus_openings.syllabus_id
      left join certificates on certificates.registration_id = registrations.id
      left join users on users.id = registrations.user_id
      where 1=1
    """
    @syllabus_id = nil
    if(params[:syllabus_id].present?)
      @syllabus_id = params[:syllabus_id]
      sql += " and syllabuses.id="+params[:syllabus_id]
    end
    @syllabus_opening_id = nil
    if(params[:syllabus_opening_id].present?)
      @syllabus_opening_id = params[:syllabus_opening_id]
      sql += " and syllabus_openings.id="+params[:syllabus_opening_id]
    end
    @ref_oode = nil
    if(params[:ref_code].present?)
      @ref_oode = params[:ref_code]
      sql += " and certificates.ref_code like '"+params[:ref_code]+"'"
    end
    @result = Registration.find_by_sql(sql)
  end

  def export_student
    require 'spreadsheet'
    sql = """
      select distinct users.title, users.fname, users.lname, syllabuses.name, users.id as user_id, syllabuses.id as syllabus_id
      from registrations
      left join syllabus_openings on syllabus_openings.id = registrations.syllabus_opening_id
      left join syllabuses on syllabuses.id = syllabus_openings.syllabus_id
      left join certificates on certificates.registration_id = registrations.id
      left join users on users.id = registrations.user_id
      where 1=1
    """
    if(params[:syllabus_id].present?)
      sql += " and syllabuses.id="+params[:syllabus_id]
    end
    if(params[:syllabus_opening_id].present?)
      sql += " and syllabus_openings.id="+params[:syllabus_opening_id]
    end
    if(params[:ref_code].present?)
      sql += " and certificates.ref_code like '"+params[:ref_code]+"'"
    end
    result = Registration.find_by_sql(sql)

    book = Spreadsheet::Workbook.new
    sheet = book.create_worksheet
    sheet.row(0).replace ['ชื่อ','Name','วันเกิด','การศึกษา','จังหวัด','email','หลักสูตร','เกรดรายวิชา(เกรดขั้นต่ำ)','เลขที่ใบรับรอง']
    row_no = 0
    result.each do |row|
      row_no += 1
      base_no = row_no
      user = User.find(row.user_id)
      sheet[row_no,0] = "#{user.title}#{user.fname} #{user.lname}"
      sheet[row_no,1] = "#{user.e_title}#{user.e_fname} #{user.e_lname}"
      sheet[row_no,2] = "#{user.dob}"
      sheet[row_no,3] = "#{Education.find(:first, :conditions=>["id=?", user.education_id]).name}"
      sheet[row_no,4] = "#{user.province}"
      sheet[row_no,5] = "#{user.email}"
      sheet[row_no,6] = row.name
      syllabus = Syllabus.find_by_id(row.syllabus_id)
      syllabus.syllabus_course_mappers.each do |mapper|
        registration_course = RegistrationCourse.find_by_sql("select min(grade) as grade from registration_courses inner join registrations on registration_courses.registration_id = registrations.id and registration_courses.course_id = "+mapper.course.id.to_s+" and registrations.user_id = "+row.user_id.to_s)
        certificate_course_rule = CertificateCourseRule.find_by_sql("select minimum_grade from certificate_course_rules where syllabus_id = "+row.syllabus_id.to_s+" and course_id = "+mapper.course.id.to_s)
        if registration_course.present? and registration_course[0].grade.present?
          if certificate_course_rule.present?
            sheet[row_no,7] = "#{mapper.course.name}: #{registration_course[0].grade} (#{certificate_course_rule[0].minimum_grade})"
          else
            sheet[row_no,7] = "#{mapper.course.name}: #{registration_course[0].grade} (-)"
          end
        else
          if certificate_course_rule.present?
            sheet[row_no,7] = "#{mapper.course.name}: - (#{certificate_course_rule[0].minimum_grade})"
          else
            sheet[row_no,7] = "#{mapper.course.name}: - (-)"
          end
        end
        row_no += 1
      end
      if syllabus.syllabus_course_mappers.size>0
        row_no -= 1
      end
      certificates = Certificate.find_by_sql("select certificates.ref_code from certificates inner join registrations on certificates.registration_id = registrations.id inner join syllabus_openings on syllabus_openings.id = registrations.syllabus_opening_id inner join syllabuses on syllabuses.id = syllabus_openings.syllabus_id where syllabus_id = "+row.syllabus_id.to_s+" and registrations.user_id = "+row.user_id.to_s+" and certificates.ref_code is not null")
      if certificates.present?
        sheet[base_no,8] = certificates[0].ref_code
      end
    end

    path = "#{RAILS_ROOT}/public/files/#{session[:user_id]}_students.xls"
    book.write path
    send_file path
  end

  def print_certificate
    @certificates
    sql = """
          select certificates.* from certificates
          inner join registrations on certificates.registration_id = registrations.id
          inner join syllabus_openings on registrations.syllabus_opening_id = syllabus_openings.id
          inner join syllabuses on syllabuses.id = syllabus_openings.syllabus_id
          where 1=1 
    """
    if(params[:syllabus_id].present?)
      sql += " and syllabuses.id="+params[:syllabus_id]
    end
    if(params[:syllabus_opening_id].present?)
      sql += " and syllabus_openings.id="+params[:syllabus_opening_id]
    end
    if(params[:ref_code].present?)
      sql += " and certificates.ref_code like '"+params[:ref_code]+"'"
    end
    @certificates = Certificate.find_by_sql(sql)
    render :layout => false
  end
  def create
    syllabus = Syllabus.new(params[:syllabus])
    syllabus.created_by = current_user.login
    syllabus.save
    flash[:notice] = 'เพิ่มหลักสูตรเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'admin'
  end
  def edit
    @syllabus = Syllabus.find params[:id]
  end
  def update
    syllabus = Syllabus.update(params[:syllabus][:id], params[:syllabus])
    syllabus.updated_by = current_user.login
    syllabus.save
    flash[:notice] = 'แก้ไขหลักสูตรเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'admin'
  end
  def delete
    syllabus = Syllabus.find params[:id]
    syllabus.destroy
    redirect_to :action=>:admin
  end
  def assign_course
    @syllabus = Syllabus.find params[:id]
    @courses = Course.find :all, :order=>:id
    @mapper_courses = SyllabusCourseMapper.find_all_by_syllabus_id(@syllabus.id).collect {|m| m.course}
  end
  def mapper
    syllabus = Syllabus.find params[:syllabus][:id]
    #delete old item
    SyllabusCourseMapper.destroy_all :syllabus_id=>syllabus.id
    
    map_items = params[:course_ids]
    if map_items
      map_items.each do |item|
        syllabus_course_mapper = SyllabusCourseMapper.new
        syllabus_course_mapper.syllabus = syllabus
        syllabus_course_mapper.course = Course.find item
        syllabus_course_mapper.created_by = current_user.login
        syllabus_course_mapper.save
      end
    end
    flash[:notice] = 'กำหนดวิชาเรียนในหลักสูตรเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'admin'
  end
  def assign_cert_course_grade
    @syllabus = Syllabus.find params[:id]
    @courses = []
    mappers = SyllabusCourseMapper.find_all_by_syllabus_id @syllabus.id
    if mappers.present?
      mappers.each do |item|
        @courses << item.course
      end
    end
    @course_grades = CertificateCourseRule.find_all_by_syllabus_id(@syllabus.id).collect {|c| c.course}
  end
  def cert_course_grade_mapper
    syllabus = Syllabus.find params[:syllabus][:id]
    #delete old item
    CertificateCourseRule.destroy_all :syllabus_id=>syllabus.id

    map_items = params[:course_ids]
    if map_items.present?
      map_items.each do |item|
        certificate_course_rule = CertificateCourseRule.new
        certificate_course_rule.syllabus = syllabus
        certificate_course_rule.course = Course.find item
        certificate_course_rule.minimum_grade = params["minimun_grade_"+item]
        certificate_course_rule.created_by = current_user.login
        certificate_course_rule.save
      end
    end
    flash[:notice] = 'กำหนดเกรดขั้นต่ำในการจบหลักสูตรเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'admin'
  end
  def manage_pre_requisite
    @syllabus = Syllabus.find params[:id]
    @courses = Course.find :all, :order=>:id
  end
  def assign_pre_requisite
    @syllabus = Syllabus.find params[:id]
    @course = Course.find params[:course_id]
    #prevent rollback pre requisite
    rollback_pre_requisite_ids = SyllabusCoursePreRequisite.find(:all, :conditions=>['syllabus_id=? and require_id=?',@syllabus.id, @course.id]).collect {|p| p.course_id}
    conditions = ['id<>?',@course.id]
    conditions = ['id<>? and id not in (?)',@course.id,rollback_pre_requisite_ids] if !rollback_pre_requisite_ids.empty?
    @courses = Course.find :all, :conditions=>conditions, :order=>:id
    @pre_requisite_course_ids = SyllabusCoursePreRequisite.find(:all, :conditions=>['syllabus_id=? and course_id=?',@syllabus.id, @course.id]).collect {|p| p.require_id}
  end
  def save_pre_requisite
    syllabus = Syllabus.find params[:syllabus][:id]
    course = Course.find params[:course_id]
    #delete old pre_requisite
    SyllabusCoursePreRequisite.destroy_all :syllabus_id=>syllabus.id, :course_id=>course.id

    pre_requisite_items = params[:pre_requisite_ids]
    if pre_requisite_items
      pre_requisite_items.each do |item|
        syllabus_course_pre_requisite = SyllabusCoursePreRequisite.new
        syllabus_course_pre_requisite.syllabus = syllabus
        syllabus_course_pre_requisite.course_id = course.id
        syllabus_course_pre_requisite.require_id = item
        syllabus_course_pre_requisite.created_by = current_user.login
        syllabus_course_pre_requisite.save
      end
    end
    flash[:notice] = 'กำหนดวิชาที่ต้องผ่านก่อนเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'manage_pre_requisite', :id=>syllabus
  end
  def opening
    @syllabus = Syllabus.find params[:id]
    @opening = SyllabusOpening.find(:first, :conditions=>['syllabus_id=? and open_flag=?',@syllabus.id,'Y'])
  end
  def new_opening
    @syllabus = Syllabus.find params[:id]
    @opening = SyllabusOpening.new
    @opening.syllabus = @syllabus
  end
  def create_opening
    #manage old opening for syllabus
    @openings = SyllabusOpening.find_all_by_syllabus_id params[:opening][:syllabus_id]
    if @openings
      @openings.each do |item|
        item.open_flag = 'C'  #N=Not Open, Y=Open, C=Close or Complete
        item.save
      end
    end
    #manage new opening for syllabus
    opening = SyllabusOpening.new params[:opening]
    opening.open_flag = 'Y'
    opening.created_by = current_user.login
    opening.save
    flash[:notice] = 'กำหนดรอบการอบรมเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'opening', :id=>params[:opening][:syllabus_id]
  end
  def edit_opening
    @opening = SyllabusOpening.find params[:id]
  end
  def update_opening
    opening = SyllabusOpening.update(params[:opening][:id], params[:opening])
    opening.updated_by = current_user.login
    opening.save
    flash[:notice] = 'แก้ไขรอบการอบรมเป็นที่เรียบร้อยแล้ว'
    redirect_to :action => 'opening', :id=>opening.syllabus.id
  end
  def opening_history
    @syllabus = Syllabus.find params[:id]
    @openings = SyllabusOpening.find_all_by_syllabus_id params[:id], :order=>'start DESC'
  end
  def delete_opening
    opening = SyllabusOpening.find params[:id]
    syllabus_id = opening.syllabus_id
    opening.destroy
    redirect_to :action => 'opening', :id=>syllabus_id
  end
end
